package com.hitrust.gs.action.productTiptopStock;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import org.apache.struts.upload.FormFile;

import system.LoginContext;

import com.hitrust.gs.business.GSProductTiptopStockProcesser;
import com.hitrust.gs.business.GSProviderProcessor;
import com.hitrust.gs.form.GSImportExcelForm;
import com.hitrust.gs.lib.GSBaseConstants;
import com.hitrust.gs.lib.GSLogWriter;
import com.hitrust.gs.service.GSBaseAction;
import com.hitrust.gs.service.GSBaseForm;
import com.hitrust.gs.util.RegulaUtil;
import com.hitrust.gs.util.date.DateUtil;
import com.hitrust.gs.util.exception.GSException;
import com.hitrust.gs.util.page.ResultProcess;
import com.hitrust.gs.vo.GSProductUpdatePriceView;
import com.hitrust.gs.vo.WarehouseMaxStorageCofigVO;

public class GSLeadingInExeclAction extends GSBaseAction {


	public static final String SHEET_NAME = "批量新增或更新";
	public static final String SEP = "%";
	public static String[] HEAD_NAMES = { "仓库编码（6位）", "产品编码", "最高库存", "状态（1有效、0无效）"};

	public boolean[] COL_CHECK = new boolean[4];

	public GSProductUpdatePriceView manuview = null;
	GSProviderProcessor ppro = new GSProviderProcessor();

	// 匹配不包括 ',",<,>,&的特殊字符
	public static final String regula = "[^'\"<>&]*";
	public static final String zhibao = "^\\d+$";


	public String execute(GSBaseForm aForm) throws Exception {
		GSImportExcelForm tGSImportExcelForm = (GSImportExcelForm) aForm;
		FormFile formFile = tGSImportExcelForm.getExcelfile();

		if (formFile != null) {
			loadDataFromExcel(formFile.getInputStream(),aForm);
		
			
			//去查数据库
		}
		return GSBaseConstants.FORWARD_SUCCESS;
	}

	/**
	 * 从Excel中载入格式化数据，数据项之间以分隔符%隔开
	 * 
	 * @param aInputStream
	 * @param aPage
	 * @param aPageSize
	 * @return
	 * @throws Exception
	 */
	public void loadDataFromExcel(InputStream aInputStream,GSBaseForm aForm)
			throws Exception {
		Workbook tExcelWorkbook = null;

		try {
			tExcelWorkbook = Workbook.getWorkbook(aInputStream);

			Sheet tExcelSheet = tExcelWorkbook.getSheet(SHEET_NAME);
			if (tExcelSheet == null) {
				aForm.getRequest().setAttribute("gohistory","true");	
				throw new GSException("批量新增或更新信息失败:文件格式不正确,无法找到：" + SHEET_NAME);
			}
			for (int i = 1; i < tExcelSheet.getRows(); i++) {
				Cell tCells[] = tExcelSheet.getRow(i);
				String tRowText = "";
				int num = tCells.length;
				for (short j = 0; j < num; j++) {
					Cell tExcelCell = tCells[j];
					if (tExcelCell == null) {
						break;
					}
					String tCellValue = tExcelCell.getContents();
					if (tCellValue.length() == 0) {
						tCellValue = " ";
					}
					tRowText += tCellValue + SEP;
				}

				for (int a = num; a < HEAD_NAMES.length; a++) {
					tRowText += " " + SEP;
				}

				// 检查数据是否正确,如果和数据库中不符则将结果添加到末尾,从第二行开始
				
				tRowText = this.checkExecl(tRowText,i, aForm);
				for (int tJ = 0; tJ < COL_CHECK.length; tJ++) {
					if (COL_CHECK[tJ] == false) {
						aForm.getRequest().setAttribute("gohistory","true");	
						throw new GSException(tRowText);
					}
				}
				GSLogWriter.info(tRowText);
			
			}
		} catch (BiffException e) {
			//GSLogWriter.error(e.getMessage());
			e.printStackTrace();
		} catch (IOException e) {
			//GSLogWriter.error(e.getMessage());
			e.printStackTrace();
		}
		finally {
			if (tExcelWorkbook != null)
				tExcelWorkbook.close();
		}
		
	}

	
	
	public String checkExecl(String aRowText,int row,GSBaseForm aForm) throws Exception {
	
		for (int i = 0; i < COL_CHECK.length; i++) {
			COL_CHECK[i] = true;
		}

		String[] tRecorderColumns = aRowText.split(SEP);

		//判断excel字段不能有特殊字符
		for (int i = 0; i < tRecorderColumns.length; i++) {
			if (!RegulaUtil.matchByString(tRecorderColumns[i], regula)) {
				COL_CHECK[i] = false;
				return HEAD_NAMES[0] + tRecorderColumns[0] + ": "
						+ HEAD_NAMES[i] + "不能为',\",<,>,&等特殊字符!";
			}
		}
		//判断excel字段不能为空
		if (tRecorderColumns[0].trim().equals("")) {
			COL_CHECK[0] = false;
			return "第"+row+"条记录，"  + HEAD_NAMES[0]
					+ "不能为空！";
		}
		
		if (tRecorderColumns[1].trim().equals("")) {
			COL_CHECK[1] = false;
			return "第"+row+"条记录，" + HEAD_NAMES[1]
					+ "不能为空！";
		}
		
		if (tRecorderColumns[2].trim().equals("")) {
			COL_CHECK[2] = false;
			return "第"+row+"条记录，"  + HEAD_NAMES[2]
					+ "不能为空！";
		}

		if (tRecorderColumns[3].trim().equals("")) {
			COL_CHECK[3] = false;
			return "第"+row+"条记录，"  + HEAD_NAMES[3]
					+ "不能为空！";
		}
		
		//仓库编码必须是t_warehouse表中存在的code而且位数为6，验证不通过提示：“第XX条记录，仓库不存在或者不是否分公司级别”
		String code = tRecorderColumns[0];
		if (code.length() != 6) {
			COL_CHECK[0] = false;
			return "第"+row+"条记录，"  +HEAD_NAMES[0]
					+ "不是分公司级别！";
		}
		GSProductTiptopStockProcesser ordProcesser = new GSProductTiptopStockProcesser();
		
		int isExistStock = ordProcesser.isExistRow(" select * from t_warehouse where  code =  '"+tRecorderColumns[0]+"'");
		if (isExistStock == 0) {
			COL_CHECK[0] = false;
			return "第"+row+"条记录，"  + HEAD_NAMES[0]
					+ "仓库不存在！";
		}
		
		//验证产品编码，产品编码必须是t_product表里concentricity_code存在的而且is_buy=1是可采购的，
		//验证不通过提示：“第XX条记录产品部存在或者不可采购”
		int isExistProduct = ordProcesser.isExistRow(" select * from t_product  where  is_buy=1 and  concentricity_code =  '"+tRecorderColumns[1]+"'");
		if (isExistProduct == 0) {
			COL_CHECK[1] = false;
			return "第"+row+"条记录，"  + HEAD_NAMES[1]
					+ "记录产品不存在或者不可采购！";
		}
		
		//最高库存必须是数字
		Pattern pattern = Pattern.compile("^[0-9]*\\.?[0-9]{0,4}");
		Matcher isNum = pattern.matcher(tRecorderColumns[2].trim());
		if (!isNum.matches()) {
			COL_CHECK[2] = false;
			return "第"+row+"条记录，"  + HEAD_NAMES[2] + "必须为数值!";
			
		} else if (Double.parseDouble(tRecorderColumns[2].trim()) <= 0) {
			COL_CHECK[2] = false;
			return "第"+row+"条记录，"  + HEAD_NAMES[2] + "不能小于等于0!";
		}
		
		//状态必须是1或者0
		if(!"1".equals(tRecorderColumns[3].trim()) && !"0".equals(tRecorderColumns[3].trim())){
			COL_CHECK[3] = false;
			return "第"+row+"条记录，"  + "状态必须是1或者0!";
		}
		
		
		//如果记录表里存在的更新记录，如果记录表里没有的新增记录
		int isExistWarehouse = ordProcesser.isExistRow("   select * from t_warehouse_max_storage_config where WAREHOUSE_CODE = '"+tRecorderColumns[0]+"' and PRODUCT_CODE = '"+tRecorderColumns[1]+"'" );
		//登录人的信息
		LoginContext loginContext = (LoginContext)aForm.getSession().getAttribute(GSBaseConstants.LOGIN);
		
		if (isExistWarehouse == 0) {
			//新增记录
			WarehouseMaxStorageCofigVO vo = new WarehouseMaxStorageCofigVO();
			vo.setWarehouse_code(tRecorderColumns[0]);
			vo.setProduct_code(tRecorderColumns[1]);
			vo.setMax_count(tRecorderColumns[2]);
			vo.setStatus(tRecorderColumns[3]);
			vo.setCreate_user(loginContext.getUser().getName());
			vo.setCarete_user_id(loginContext.getUser().getId());
			String currentDateStr = DateUtil.getCurrentDate("yyyy-MM-dd HH:mm:ss");
			vo.setCreate_time(currentDateStr);
			ordProcesser.insertWarehouseMaxStorageConfig(vo);
		}else{
			//更新记录
			WarehouseMaxStorageCofigVO vo = new WarehouseMaxStorageCofigVO();
			vo.setWarehouse_code(tRecorderColumns[0]);
			vo.setProduct_code(tRecorderColumns[1]);
			vo.setMax_count(tRecorderColumns[2]);
			vo.setStatus(tRecorderColumns[3]);
			vo.setLast_update_user(loginContext.getUser().getName());
			vo.setLast_update_user_id(loginContext.getUser().getId());
			String currentDateStr = DateUtil.getCurrentDate("yyyy-MM-dd HH:mm:ss");
			vo.setLast_update_time(currentDateStr);
			ordProcesser.updateWarehouseMaxStorageConfig(vo);
		}
		
		aForm.getRequest().setAttribute("message","批量新增或更新信息成功！");
		return "ok";
	}
}
